"""
## .. module:: manageDB

Manages databases.   

##
"""
import sqlite3
import urllib
import time
import sys
import datetime


"""
##.. function:: merge(fromCur, toCur)
    
Merge data from ``fromCur`` to ``toCur``.  By default, the operation is an
``INSERT OR IGNORE``: although this is slow, it avoids duplicate entries.

Paramaters
!!!!!!!!!!
fromCur
    A connection to the database with the new data.
toCur
    A connection to the final database that will hold all data.


Usage
!!!!!
Pythonic
@@@@@@@@
::

    fromDB=sqlite3.connect(sys.argv[1])
    toDB=sqlite3.connect(sys.argv[2])
    fromCur=fromDB.cursor()
    toCur=toDB.cursor()

    # READ FROM 'FROMDB'.
    fromCur.execute('select * from data order by dateTime')

    # IF NECESSARY, CREATE TABLE IN 'TODB'.
    try:
        toCur.execute('CREATE TABLE data(dateTime  DATETIME ' + \
                        'PRIMARY KEY, kW FLOAT, V FLOAT)')
        toDB.commit();
    except:
        print "Table already exists"

    merge(fromCur, toCur)

Command-line
@@@@@@@@@@@@
::

    python manageDB.py FROM_DB_NAME TO_DB_NAME
##
"""
def merge(fromCur, toCur):
    # If necessary, create table in 'to':
    try: 
        toCur.execute('CREATE TABLE data(dateTime  DATETIME ' + \
                        'PRIMARY KEY, kW FLOAT, V FLOAT)')
        toDB.commit();
    except: 
        print "Table already exists"
    
    # There may overlap between tables.  To avoid duplicate entries, we use
    # INSERT OR IGNORE.  However, since this is (probably?) slower than a plan
    # INSERT, we only do this if there is an overlap.  This, of course, assumes
    # that both tables are ordered chronologically.

    # Find maximum dateTime in 'to':
    max=toCur.execute('select max(dateTime) from data;').fetchone()[0]
    max=time.strptime(max, "%Y-%m-%d %H:%M:%S")
    max=datetime.datetime(max.tm_year, max.tm_mon, max.tm_mday, max.tm_hour,
                          max.tm_min, max.tm_sec)

    # Read data from 'from': 
    fromCur.execute('select * from data order by dateTime')
    rows=fromCur.fetchall()

    sqlText = 'begin; '
    count=0; totalCount=0

    # To begin with, with assume that there may be an overlap between tables:
    overlap=True
    for row in rows:
        if overlap:
            dt=time.strptime(row[0], "%Y-%m-%d %H:%M:%S")
            dt=datetime.datetime(dt.tm_year, dt.tm_mon, dt.tm_mday, 
                                 dt.tm_hour, dt.tm_min, dt.tm_sec)

            # Check that there is still overlap:
            if dt > max: overlap=False

            # IGNORE duplicate entries:
            sqlText += 'INSERT OR IGNORE INTO data '

        else:        
            # If there's no overlap, we can just insert directly.
            sqlText += 'INSERT INTO data '
        
        # Insert the actual values:
        sqlText += 'VALUES(datetime("%s"), %s, %s); ' %\
                    (row[0], row[1], row[2])

        # SQLite needs two disk rotations per commit.  To we spread this cost
        # over 5000 commits:
        if count == 5000:
            print 'Inserted ' + str(totalCount)
            toCur.executescript(sqlText + ' commit;')
            toDB.commit()

            # And reset:
            sqlText = 'begin; '
            totalCount += count
            count = 0
        else: count +=1

    # Final commit:
    toCur.executescript(sqlText + ' commit;')


if __name__ == "__main__":
    # Connect to databases: 
    fromDB=sqlite3.connect(sys.argv[1])
    toDB=sqlite3.connect(sys.argv[2])
    fromCur=fromDB.cursor()
    toCur=toDB.cursor()

    # Merge: 
    merge(fromCur, toCur)

    # And close: 
    toDB.commit()
    fromDB.close()
    toDB.close()


#///////////////////////////////////////////////////////////////////////////////
# This is here for historical reasons.....
def migrate(fromCur, toCur):
    rows=fromCur.fetchall()
    count=0;
    totalCount=0
    for row in rows:
        if count == 0:
            sqlText = 'begin;'
        date, time = row[0].split()
        m, d, y = date.split('/')
        date = '-'.join([y,m,d])
        sqlText += ' INSERT INTO data VALUES(datetime("%s %s"), %s, %s); ' %\
                    (date, time, row[1], row[2])

        count +=1;
        if count == 5000:
            totalCount += count
            count = 0
            print 'inserted ' + str(totalCount)
            sqlText += ' commit;'
            fromCur.executescript(sqlText)
            toDB.commit()

    sqlText += ' commit;'
    fromCur.executescript(sqlText)
#///////////////////////////////////////////////////////////////////////////////
